<?php
/**
 * CK 开发框架
 * User: Clake
 * Date: 15/6/18
 * Time: 00:45
 */

namespace CK\Database;


class Table {
//build sql script
    public $sql_str;
    /**
     * dba class
     * @var DBA
     */
    private $_dba;
    private $_tables;
    private $_select_arr;
    private $_where_arr;
    private $_join_arr;
    private $_group_arr;
    private $_limit_str;
    private $_order_str;
    private $_limit_type;
    private $_table_name;
    private $_convert_flag;
    private $_convert_code;
    private $_params;
    private $_is_cache;
    private $_cache_expire;
    private $_cache_exist;
    public $_cache_key;
    private $_key_append;
    private $_is_distinct;
    private $_rules;

    //limit type
    const LIMIT_NUM = "number";
    const LIMIT_PAGE = "page";

    public function __construct($data_source,$table,$is_cache=false,$cache_expire=120,$cache_key=null,$key_append=false){
        $this->_dba = $data_source;
        $this->_table_name = $table;
        $this->_tables[$table] = [
            "where"=>"",
            "join" => "",
            "group" => "",
            "select" => "",
            "order" => "",
            "limit" => ""
        ];
        $this->sql_str = "";
        $this->_params = [];
        $this->_is_cache = $is_cache;
        $this->_cache_expire = $cache_expire;
        $this->_cache_exist = false;
        $this->_cache_key = $cache_key;
        $this->_key_append = $key_append;
        $this->_is_distinct = false;
        $this->_rules = null;
    }

    public function clear() {
        $this->_tables[$this->_table_name] = [
            "where"=>"",
            "join" => "",
            "group" => "",
            "select" => "",
            "order" => "",
            "limit" => ""
        ];
        $this->_select_arr = null;
        $this->_where_arr = null;
        $this->_join_arr = null;
        $this->_group_arr = null;
        $this->_limit_str = "";
        $this->_order_str = "";
        $this->_limit_type = null;
        $this->_params = [];
        $this->_is_distinct = false;
        $this->_rules = null;
        return $this;
    }

    /**
     * 设置查询是否启用缓存,默认为不启用
     * @param bool $is_cache <是否启用缓存>
     * @param int $cache_expire [可选] <缓存过期时间,默认为120秒>
     * @param string $cache_key [可选] <可指定缓存的key值>
     * @param bool $key_append [可选] <自义定的缓存KEY值是否附加到原KEY值里,如果为false那查询的缓存KEY为cache_key的名字>
     */
    public function cache($is_cache,$cache_expire=120,$cache_key=null,$key_append=false) {
        $this->_is_cache = $is_cache;
        $this->_cache_expire = $cache_expire;
        $this->_cache_key = $cache_key;
        $this->_key_append = $key_append;
    }

    /**
     * 设置要取得的表字段名列表
     * @param array:assoc $columns <表字段名例表>
     * @param string $table [可选] <设置表名,默认为当前表>
     * @return Table <返回当前实体类>
     */
    public function select($columns,$table=null){
        $table = $table ? $table : $this->_table_name;
        $sql = "";
        foreach ($columns as $column => $t) {
            $column_arr = explode(" ", $column);
            $t_clone = trim($t) !== "" ? $this->_dba->format($t) : $this->_dba->format($table);

            if (count($column_arr) == 2) {
                $sql .= $t_clone.".".$this->_dba->format($column_arr[0])." AS '{$column_arr[1]}',";
            } elseif (count($column_arr) == 3) {
                if ($column_arr[0] === 'eval') {
                    $sql .= "({$column_arr[1]}) AS '{$column_arr[2]}',";
                } else {
                    $sql .= $column_arr[0]."(".$t_clone.".".$this->_dba->format($column_arr[1]).") AS '{$column_arr[2]}',";
                }
            } else {
                $column_arr[0] = $column_arr[0] == "*" ? $column_arr[0]:$this->_dba->format($column_arr[0]);
                $sql .= $t_clone.".".$column_arr[0].",";
            }
        }

        $this->_select_arr[$table] = $columns;
        $this->_tables[$table]["select"] .= $sql;
        return $this;
    }

    public function select_str($str,$table = null) {
        $table = $table ? $table : $this->_table_name;
        $this->_tables[$table]["select"] .= $str.',';
        return $this;
    }

    /**
     * 设置查询条件
     * @param array:assoc $where_field <查询条件列表,例:['name'=>"clake"],
     * 									['name'=>"OR 'clake'"],
     * 									['name'=>"OR >10"],
     * 									['name'=>"OR IN (10,11,32)"]
     * 									>
     * @param string $table [可选] <条件表名,默认为当前表>
     * @param string $pre   [可选] <在条件前加上 AND 或 OR 或其它>
     * @return Table <返回当前实体类>
     */
    public function where($where_field,$table=null,$pre=''){
        if (!is_array($where_field)) {
            return $this;
        }
        $table = $table ? $table : $this->_table_name;
        $where_str = $this->_dba->where_recursion($where_field,'AND',$table);
        $this->_where_arr[$table] = (isset($this->_where_arr[$table])?$this->_where_arr[$table]:[]) + $where_field;
        $this->_tables[$this->_table_name]["where"] .= $pre. $where_str;
        return $this;
    }

    /**
     * 设置查询条件,自动在整个条件前加上 AND
     * @param      $where_field
     * @param null $table
     *
     * @return Table
     */
    public function where_and($where_field,$table=null) {
        return $this->where($where_field,$table,' AND ');
    }

    /**
     * 设置查询条件,自动在整个条件前加上 OR
     * @param      $where_field
     * @param null $table
     *
     * @return Table
     */
    public function where_or($where_field,$table=null) {
        return $this->where($where_field,$table,' OR ');
    }

    /**
     * 设置查询条件,用原始SQL WEHRE
     * @param string $str <条件字符串>
     * @return Table <返回当前实体类>
     */
    public function where_str($str) {
        $this->_tables[$this->_table_name]["where"] .= $str;
        return $this;
    }

    /**
     * 绑定设置的条件字段值
     * @param string $name <字段值名,如 :name>
     * @param string $value <要绑定的值>
     * @return Table <返回当前实体类>
     */
    public function buildParam($name,$value) {
        $this->_params[$name] = $value;
        return $this;
    }

    /**
     * 设置多表连接
     * @param string $table_t <要连接到的表名>
     * @param string $column_t <连接到表的字段名>
     * @param string $table_f <从什么表连接>
     * @param string $column_f <从什么字段连接>
     * @param string $join [可选] <连接类型,默认为INNER>
     * @return Table <返回表的实体类>
     */
    public function join($table_t,$column_t,$table_f,$column_f,$join="INNER"){
        $tmp = explode(" ", $table_t);
        $table_t_c = "";
        if (count($tmp) > 1) {
            $table_t_c = "AS ".$this->_dba->format($tmp[1]);
            $table_t = $tmp[1];
            $table = $tmp[0];
        } else {
            $table = $table_t;
        }

        $table = $this->_dba->format($table);
        $table_f = $this->_dba->format($table_f);
        $table_t = $this->_dba->format($table_t);
        $column_t = $this->_dba->format($column_t);
        $column_f = $this->_dba->format($column_f);
        $join = strtoupper($join);


        $this->_join_arr[$table_t] = [
            "table" => $table,
            "t_t" => $table_t,
            "t_t_c" => $table_t_c,
            "c_t" => $column_t,
            "t_f" => $table_f,
            "c_f" => $column_f,
            "join" => $join
        ];

        $this->_tables[$this->_table_name]["join"] .= " $join JOIN $table $table_t_c ON $table_t.$column_t=$table_f.$column_f\n";
        return $this;
    }

    /**
     * 设置多个JOIN表连接
     * 参数DEMO:
     * $joins = [
     *     ['tabA.column','tabB.column','INNER'],
     *     ['tabC.column','tabB.column','LEFT'],
     *     ['tabD.column','tabB.column']
     * ]
     *
     * @param array $joins
     *
     * @return $this
     */
    public function joins($joins) {
        if (is_array($joins)) {
            foreach ($joins as $join) {
                $to = explode('.',$join[0]);
                $from = explode('.',$join[1]);
                $key = isset($join[2])?$join[2]:'INNER';
                $this->join($to[0],$to[1],$from[0],$from[1],$key);
            }
        }
        return $this;
    }

    /**
     * 设置表分组
     * @param string|array $column <如果值为string就只设置一个字段,如果为array就设置多个字段分组>
     * @param string $table [可选] <要分组的表名,默认为当前表>
     * @return Table <返回当前实体类>
     */
    public function group($column,$table=null){
        $table = $table ? $table : $this->_table_name;
        if (is_array($column)) {
            $tmp = [];
            foreach ($column as $cln => $tab) {
                $tab = trim($tab)==""?$table:$tab;
                $tmp[] = $this->_dba->format($tab).".".$this->_dba->format($cln);
            }
            $column_str = implode(",", $tmp);
        } else {
            $column_str = $this->_dba->format($table).".".$this->_dba->format($column);
        }
        $this->_group_arr[$table] = $column;
        $this->_tables[$this->_table_name]["group"] = "GROUP BY $column_str";
        return $this;
    }

    /**
     * 设置表排序字段
     * @param string|array $columns <如果值为string就只设置一个字段,如果为array就设置多个字段排序>
     * @param string $order_type [可选] <排序规则,默认为倒序 DESC>
     * @param string $table [可选] <排序的表名,默认为当前表>
     * @return Table <返回当前实体类>
     */
    public function order($columns,$order_type="DESC",$table=null){
        $table = $table ? $table : $this->_table_name;
        if (is_array($columns)) {
            $column_arr = array();
            foreach ($columns as $column => $tab) {
                $clns = explode(' ', $column);
                $type = $order_type;
                if (count($clns) > 1) {
                    $column = $clns[0];
                    $type = $clns[1];
                }
                if (trim($tab) == "") {
                    $column_arr[] = $this->_dba->format($table).".".$this->_dba->format($column)." $type";
                } else {
                    $column_arr[] = $this->_dba->format($tab).".".$this->_dba->format($column)." $type";
                }
            }
            $this->_order_str = "ORDER BY ".implode(",", $column_arr);
        } else {
            $columns = $this->_dba->format($table).".".$this->_dba->format($columns);
            $this->_order_str = "ORDER BY $columns $order_type";
        }
        return $this;
    }

    public function orderIn($columns,$rule) {
        $columns = $this->_dba->format($this->_table_name).".".$this->_dba->format($columns);
        $list_str = join(',',$rule);
        $this->_order_str = "ORDER BY FIND_IN_SET($columns,'$list_str')";
        return $this;
    }

    /**
     * 特殊自定义order字符串
     * @param string $order
     *
     * @return self <返回当前实体类>
     */
    public function order_str($order) {
        $this->_order_str = 'ORDER BY '.$order;
        return $this;
    }

    /**
     * 设置分页数据或限制返回记录数
     * @param number $number <需要得到的记录数>
     * @param string $page [可选] <要得到的分页,如果不给这个值就不会拿分页数据,只以第一个参数的记录数返回>
     * @return Table <返回当前实体类>
     */
    public function limit($number,$page=null){
        if ($this->_dba->db_type == DBA::DBA_MSSQL) {
            if ($page == null) {
                $this->_limit_type = self::LIMIT_NUM;
                $this->_limit_str = " TOP $number ";
            } else {
                $this->_limit_type = self::LIMIT_PAGE;
                $page = intval($page);
                if ($page > 1) {
                    $start = ($page - 1) * $number + 1;
                } else {
                    $start = 1;
                }
                $end = $page * $number;
                $this->_limit_str = " WHERE pa.RowNumber BETWEEN $start AND $end";
            }
        } else {
            if ($page == null) {
                $this->_limit_type = self::LIMIT_NUM;
                $this->_limit_str = "LIMIT $number";
            } else {
                $this->_limit_type = self::LIMIT_PAGE;
                $page = intval($page);
                if ($page > 1) {
                    $curpage = ($page-1)*$number;
                } else {
                    $curpage = 0;
                }

                $this->_limit_str = "LIMIT $curpage,$number";
            }
        }
        return $this;
    }

    /**
     * 设置结果集唯一记录返回
     * @param bool $flag <true or false>
     * @return Table <返回当前实体类>
     */
    public function set_distinct($flag) {
        $this->_is_distinct = $flag;
        return $this;
    }

    /**
     * 得到当前表记录的总数
     * @return integer <返回记录总数>
     */
    public function get_rows() {
        $table = $this->_table_name;
        $join_str = $this->_tables[$table]["join"];
        if ($this->_tables[$table]["where"]) {
            $where_str = "WHERE ".$this->_tables[$table]["where"];
        } else {
            $where_str = '';
        }

        $group_str = $this->_tables[$table]["group"];
        $distinct_str = $this->_is_distinct ? 'DISTINCT':'';

        $select_str = "";
        foreach($this->_tables as $table => $str){
            $select_str .= $str["select"];
        }
        if ($select_str != "") {
            $select_str = substr($select_str, 0,strlen($select_str)-1);
        } else {
            $select_str = "*";
        }


        foreach ($this->_params as $key => $value) {
            $where_str = str_replace($key, $value, $where_str);
        }
        return $this->_dba->get_num_rows($table,$join_str,$where_str,$group_str,$select_str,$distinct_str);
    }

    /**
     * 执行整个查询
     * @return Table <返回当前实体类>
     */
    public function execute() {
        global $cache;
        $select_str = "";
        foreach($this->_tables as $table => $str){
            $select_str .= $str["select"];
        }

        if ($select_str != "") {
            $select_str = substr($select_str, 0,strlen($select_str)-1);
        } else {
            $select_str = "*";
        }

        $join_str = $this->_tables[$this->_table_name]["join"];

        $where_str = '';
        if ($this->_tables[$this->_table_name]["where"]) {
            $where_str = "WHERE ".$this->_tables[$this->_table_name]["where"];
        }

        $group_str = $this->_tables[$this->_table_name]["group"];

        $limit_str = $this->_limit_str;

        $order_str = $this->_order_str;

        $distinct_str = $this->_is_distinct ? 'DISTINCT':'';

        $table = $this->_dba->format($this->_table_name);

        if ($this->_dba->db_type == DBA::DBA_MSSQL) {
            if ($this->_limit_type == self::LIMIT_NUM) {
                $sql = "SELECT {$this->_limit_str}$select_str FROM $table $join_str $where_str $group_str $order_str";
            } elseif ($this->_limit_type == self::LIMIT_PAGE) {
                $sql = "SELECT * FROM (
					SELECT $select_str,
					ROW_NUMBER() OVER ($order_str) AS 'RowNumber'
					FROM $table
					$join_str
					$where_str
					$group_str
					) pa
					{$this->_limit_str}";
            } else {
                $sql = "SELECT $distinct_str $select_str FROM $table $join_str $where_str $group_str $order_str $limit_str";
            }
        } else {
            $sql = "SELECT $distinct_str $select_str FROM $table $join_str $where_str $group_str $order_str $limit_str";
        }

        foreach ($this->_params as $key => $value) {
            $sql = str_replace($key, $value, $sql);
        }

        if ($this->_convert_flag) {
            $sql = mb_convert_encoding($sql, $this->_convert_code["to_code"], $this->_convert_code["from_code"]);
        }
        $this->sql_str = $sql;
        if ($this->_is_cache) {
            $this->_cache_key = $this->_cache_key?$this->_key_append?'sql_'.md5($sql).'_'.$this->_cache_key:$this->_cache_key:'sql_'.md5($sql);
            $this->_cache_exist = $cache->get($this->_cache_key) === false?false:true;
            if (!$this->_cache_exist) {
                $this->_dba->query($sql);
            }
        } else {
            $this->_dba->query($sql);
        }
        return $this;
    }

    /**
     * 设置返回记录是否转码
     * @param string $to_code <要转到的字符编码>
     * @param string $from_code <原始编码>
     * @return Table <返回当前实体类>
     */
    public function convertCoding($to_code,$from_code) {
        $this->_convert_flag = true;
        $this->_convert_code = array(
            "to_code" => $to_code,
            "from_code" => $from_code
        );
        return $this;
    }

    /**
     * 得到整个记录集
     * @param string $type [可选] <返回的记录集类型,默认为 DBA_ASSOC>
     * @return array <返回一个2维数组>
     */
    public function get_result($type=DBA::DBA_ASSOC,$callback=null){
        global $cache;
        $list = null;
        if ($this->_is_cache) {
            if ($this->_cache_exist) {
                $list = $cache->get($this->_cache_key);
            } else {
                $list = $this->_dba->fetch_all($type);
                $cache->set($this->_cache_key, $list,$this->_cache_expire);
            }
        } else {
            $list = $this->_dba->fetch_all($type);
        }
        if ($this->_convert_flag) {
            $length = count($list);
            for ($i=0;$i<$length;$i++) {
                $result = $list[$i];
                foreach ($result as $key => $value) {
                    $result[$key] = mb_convert_encoding($value, $this->_convert_code["to_code"],$this->_convert_code["from_code"]);
                }
                $list[$i] = $result;
            }
        }
        return $list;
    }

    /**
     * 得到一条记录
     * @param string $type [可选] <返回的记录集类型,默认为 DBA_ASSOC>
     * @return array:assoc <返回一条记录>
     */
    public function get_result_one($type=DBA::DBA_ASSOC) {
        global $cache;
        $result = null;
        if ($this->_is_cache) {
            if ($this->_cache_exist) {
                $result = $cache->get($this->_cache_key);
            } else {
                $result = $this->_dba->fetch_one($type);
                $cache->set($this->_cache_key, $result, $this->_cache_expire);
            }
        } else {
            $result = $this->_dba->fetch_one($type);
        }
        if ($this->_convert_flag) {
            if ($result) {
                foreach ($result as $key => $value) {
                    $result[$key] = mb_convert_encoding($value, $this->_convert_code["to_code"],$this->_convert_code["from_code"]);
                }
            }
        }
        return $result;
    }

    /**
     * 添加验证字段
     * @param array $rules <验证字段>
     */
    public function validate($rules) {
        $this->_rules = $rules;
    }

    /**
     * 向表里新增数据
     * @param array $insert <要新增的数据,2维数据自动多条插入>
     * @param bool $insert_id 是否返回自增ID
     * @return boolean
     */
    public function insert($insert,$insert_id=false) {
        if ($this->_rules) {
            if ($flag = $this->_dba->check($insert,$this->_rules)) {
                $flag = $this->_dba->insert_row($insert, $this->_table_name,is_assoc($insert)?false:true);
                return $flag && $insert_id?$this->_dba->get_insert_id():$flag;
            } else {
                return false;
            }
        } else {
            $flag = $this->_dba->insert_row($insert, $this->_table_name,is_assoc($insert)?false:true);
            return $flag && $insert_id?$this->_dba->get_insert_id():$flag;
        }
    }

    /**
     * 更新表数据
     * @param array $update <要更新的数据ASSOC格式>
     * @return boolean <是否更新成功>
     */
    public function update($update) {
        if ($this->_rules) {
            if ($flag = $this->_dba->check($update,$this->_rules)) {
                return $this->_dba->update_row($update, $this->_table_name,$this->_where_arr[$this->_table_name]);
            } else {
                return false;
            }
        } else {
            return $this->_dba->update_row($update, $this->_table_name, $this->_where_arr[$this->_table_name]);
        }
    }

    /**
     * 删除数据,如果给了表条件以表条件删除
     */
    public function delete() {
        return $this->_dba->delete_row($this->_table_name,$this->_where_arr[$this->_table_name]);
    }

    /**
     * 是否自动添加数据库定界符
     * @param bool $flag
     */
    public function set_dadds($flag) {
        $this->_dba->setAutoAdds($flag);
    }
}